#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import _mysql, _mysql_exceptions
def connect_to_base_dicts(db='test'):
    host = '10.5.20.16'
    port = 58885
    user = 'ec_stat'
    passwd = 'L#c8UM5RGh5C'
    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh

def query(dbh, q):
    try:
        dbh.query(q)
    except _mysql.MySQLError, e:
        print e
        print q
        return None

    result = dbh.store_result()
    if result is None:  # the sql no response
        return True
    rows = result.fetch_row(0)
    return rows

#获取table_prefix
def get_channel_info():
    q = 'select channel_id, table_prefix from channel_define'
    info = {}
    conn = connect_to_base_dicts('dbwww58com_base_dicts')
    r = query(conn, q)
    for one in r:
        info[one[0]] = one[1]
    conn.close()
    return info

def create_table_summary():
    q='''
CREATE TABLE if not exists `post_summary_new` (
    `stat_date` date NOT NULL COMMENT '统计日期',
    `infos` bigint(20) NOT NULL COMMENT '帖子数',
    `users` bigint(20) NOT NULL COMMENT '帖子人数',
     PRIMARY KEY (`stat_date`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
'''
    print q
    dbh = connect_to_base_dicts('dbwww58com_58_stats')
    query(dbh, q)
    dbh.close()
def create_table(tablename):
    q = '''\
CREATE TABLE if not exists %s (
  `stat_date` date NOT NULL COMMENT '统计日期',
   `local_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '区域id',
  `dispcity1` varchar(50) NOT NULL DEFAULT '' COMMENT '一级城市',
  `dispcity2` varchar(50) NOT NULL DEFAULT '' COMMENT '二级城市',
  `dispcity3` varchar(50) NOT NULL DEFAULT '' COMMENT '三级城市',
  `cate_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '类别id',
  `belong_cate1` varchar(50) NOT NULL DEFAULT '' COMMENT '一级类别',
  `belong_cate2` varchar(50) NOT NULL DEFAULT '' COMMENT '二级类别',
  `total_info_count` int(11) DEFAULT '0' COMMENT '总帖子数',
  `total_info_users` int(11) DEFAULT '0' COMMENT '总发帖人数',
  `valid_info_count` int(11) DEFAULT '0' COMMENT '总有效帖子数',
  `valid_info_users` int(11) DEFAULT '0' COMMENT '总有效发帖人数',
  `new_info_count` int(11) DEFAULT '0' COMMENT '总新发帖子数',
  `new_info_users` int(11) DEFAULT '0' COMMENT '总新发帖子人数',
  `modify_info_count` int(11) DEFAULT '0' COMMENT '总修改帖子数',
  `modify_info_users` int(11) DEFAULT '0' COMMENT '总修改帖子人数',
  `refresh_info_count` int(11) DEFAULT '0' COMMENT '总刷新帖子数',
  `refresh_info_users` int(11) DEFAULT '0' COMMENT '总刷新帖子人数',
  `new_valid_info_count` int(11) DEFAULT '0' COMMENT '新增有效帖子数',
  `new_valid_info_users` int(11) DEFAULT '0' COMMENT '新增有效发帖人数',
  `ordinary_valid_info_count` int(11) DEFAULT '0' COMMENT '个人有效帖子数',
  `ordinary_valid_info_users` int(11) DEFAULT '0' COMMENT '个人有效帖子人数',
  `ordinary_new_info_count` int(11) DEFAULT '0' COMMENT '个人新发帖子数',
  `ordinary_new_info_users` int(11) DEFAULT '0' COMMENT '个人新发帖子人数',
  `ordinary_modify_info_count` int(11) DEFAULT '0' COMMENT '个人修改帖子数',
  `ordinary_modify_info_users` int(11) DEFAULT '0' COMMENT '个人修改帖子人数',
  `ordinary_refresh_info_count` int(11) DEFAULT '0' COMMENT '个人刷新帖子数',
  `ordinary_refresh_info_users` int(11) DEFAULT '0' COMMENT '个人刷新帖子人数',
  `ordinary_new_valid_info_count` int(11) DEFAULT '0' COMMENT '个人新增有效帖子数',
  `ordinary_new_valid_info_users` int(11) DEFAULT '0' COMMENT '个人新增有效发帖人数',
  `vip_valid_info_count` int(11) DEFAULT '0' COMMENT 'VIP有效帖子数',
  `vip_valid_info_users` int(11) DEFAULT '0' COMMENT 'VIP有效帖子人数',
  `vip_new_info_count` int(11) DEFAULT '0' COMMENT 'VIP新发帖子数',
  `vip_new_info_users` int(11) DEFAULT '0' COMMENT 'VIP新发帖子人数',
  `vip_modify_info_count` int(11) DEFAULT '0' COMMENT 'VIP修改帖子数',
  `vip_modify_info_users` int(11) DEFAULT '0' COMMENT 'VIP修改帖子人数',
  `vip_refresh_info_count` int(11) DEFAULT '0' COMMENT 'VIP刷新帖子数',
  `vip_refresh_info_users` int(11) DEFAULT '0' COMMENT 'VIP刷新帖子人数',
  `vip_new_valid_info_count` int(11) DEFAULT '0' COMMENT 'VIP新增有效帖子数',
  `vip_new_valid_info_users` int(11) DEFAULT '0' COMMENT 'VIP新增有效发帖人数',
  PRIMARY KEY (`stat_date`,`local_id`,`cate_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
''' % (tablename)
    dbh = connect_to_base_dicts()
    # mysqltool.droptable(dbh, tablename)
    query(dbh, q)

#CREATE TABLE `post_summary` (
#  `stat_date` date NOT NULL COMMENT '统计日期',
#  `infos` bigint(20) NOT NULL COMMENT '帖子数',
#  `users` bigint(20) NOT NULL COMMENT '帖子人数',
#  PRIMARY KEY (`stat_date`)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8;

if __name__ == '__main__':
#    channel_info = set(get_channel_info().values())
#    for c in channel_info:
#        create_table(c + '_post_detail_201403')
    create_table_summary()
    print('create table succeed!')